create table code_master(
 code_no number primary key,
 code_type varchar2(50) not null,
 code_code varchar2(50) not null,
 code_name varchar2(200) not null
);
drop table code_master;

create sequence code_seq;
drop sequence code_seq;

SELECT * FROM CODE_MASTER;

insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'0000','A000','성별');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'0000','B000','회원등급');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'0000','C000','과목');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'0000','D000','학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'0000','E000','학력');

insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'A000','A001','남자');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'A000','A002','여자');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'A000','A003','무관');

insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'B000','B001','학생');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'B000','B002','선생님');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'B000','B003','무관');

insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C101','국어');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C201','인문계 수학');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C202','자연계 수학');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C301','영어');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C401','물리');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C402','화학');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C403','생물');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C501','한국사');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C502','한국지리');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C503','경제');

insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D101','초등학교 1학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D102','초등학교 2학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D103','초등학교 3학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D104','초등학교 4학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D105','초등학교 5학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D106','초등학교 6학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D107','초등학교 무관');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D201','중학교 1학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D202','중학교 2학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D203','중학교 3학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D204','중학교 무관');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D301','고등학교 1학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D302','고등학교 2학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D303','고등학교 3학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D304','고등학교 무관');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D401','재수생');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D402','일반인');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D501','전체 무관');

insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E101','대학교 1학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E102','대학교 2학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E103','대학교 3학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E104','대학교 4학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E201','학사');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E202','석사');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E203','박사');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E204','전문선생님');

create table notice(
 no number primary key,
 title varchar2(200) not null,
 dateposted date not null,
 writer varchar2(50) not null,
 hits number default 0,
 orgfilename varchar2(200),
 savfilename varchar2(200),
 content varchar2(3000) not null
);
drop table notice;

create sequence notice_seq;
drop sequence notice_seq;

insert into notice(no,title,dateposted,writer,content) values(notice_seq.nextval,'테스트',sysdate,'관리자','테스트용 입니다.');

select * from notice;

create table help(
 no number primary key,
 id varchar2(50) not null,
 title varchar2(200) not null,
 dateposted date not null,
 hits number default 0,
 content varchar2(3000) not null,
 ref number default 0,
 restep number default 0,
 relevel number default 0
);
drop table help;

create sequence help_seq;
drop sequence help_seq;

insert into help(no,id,title,content,dateposted) 
values(help_seq.nextval,'admin','테스트','테스트입니다.',sysdate);

select * from help;

select * from addresscode where dong='나운2동';
--선생님 테이블

drop table wmt_teacher;

create table wmt_teacher(
 	teacherid varchar2(50) primary key, 
	name varchar2(50) not null,
	password varchar2(50) not null,
	gender varchar2(50) not null,
	tel varchar2(50) not null,
	hoperegion varchar2(50) not null,
	email varchar2(50) not null,
	questionPass varchar2(100) not null,
	answerpass varchar2(100) not null, 
	originalfilename varchar2(100),
	savefilename varchar2(100),
	enrolldate varchar2(50) not null,
	withdrawaldate varchar2(50) not null,
	university varchar2(50) not null,
	hopesubject varchar2(50) not null,
	hopegender varchar2(50) not null,
	availablestudent varchar2(50) not null,
	availableday varchar2(50) not null,
	availabletime varchar2(50) not null,
	intro varchar2(1000) not null
 )
 --학생 테이블
  create table wmt_student(
   studentid varchar2(50) primary key,
   name varchar2(50) not null,
   password varchar2(50) not null,
   gender varchar2(50) not null,
   tel varchar2(50) not null,
   hoperegion varchar2(50) not null,
   email varchar2(50) not null,
   questionpass varchar2(100) not null,
   answerpass varchar2(100) not null,
   enrolldate varchar2(50) not null,
   withdrawaldate varchar2(50) not null,
   hopesubject varchar2(150) not null,
   zipcode varchar2(10) not null,
   codeaddress varchar2(200) not null,
   subaddress varchar2(150) not null
   
  )

  --선생님,학생 리스트 인서트 sql
   insert into WMT_teacher values('java','아이유','1111','여','1234','서울','iu@naver.com','할아버지 성함?','아이유할배',
  'iu','new iu','0619','0620','중앙대','수학','여','중3','월','3시','안냥');
  
 insert into WMT_student values('java2','수지','1111','여','1234','분당','suzi@naver.com','할아버지 성함?','수지할배',
  '0619','0620','수학','20','성남시 분당구', '703동 502호');

  
  
  --선생님이 찜한 리스트
 create table wmt_teacher_lessonlist(
	no number primary key,
	teacherid varchar2(50) not null,
	lessonno number not null,
	CONSTRAINT fk_teacherlesson_teacher 
	foreign key(teacherid) references wmt_teacher(teacherid),
	CONSTRAINT fk_teacherlesson_lesson 
	foreign key(lessonno) references wmt_lesson(lessonno)
)
--학생이 선생이 찜한 리스트
create table wmt_student_teacherlist(
	no number primary key,
	studentid varchar2(50) not null,
	teacherid varchar2(50) not null,
	CONSTRAINT fk_studentteacher_student 
	foreign key(studentid) references wmt_student(studentid),
	CONSTRAINT fk_studentteacher_teacher 
	foreign key(teacherid) references wmt_teacher(teacherid)
)
insert into wmt_lesson values(lessonno_seq.nextval,'수업등록 테스트','수업등록 글쓰기 테스트','수학','0',sysdate,'3시','연결가능','java','brooklerin')
select * from WMT_LESSON
alter table wmt_lesson add(id varchar2(50) not null);
alter table wmt_lesson add(school varchar2(100) not null);
alter table wmt_lesson add(name varchar2(50) not null);
 alter table wmt_lesson rename column lessonno to no;
 --추가
 alter table wmt_lesson add(hoperegion varchar2(200) not null);
 alter table wmt_lesson add(email varchar2(200) not null);
hoperegion,email,
 --수업등록 테이블
CREATE TABLE wmt_lesson(
	no number primary key,
	id varchar2(50) not null,
	name varchar2(50) not null,
	school varchar2(100) not null,
	title varchar2(100) not null,
	content varchar2(2000),
	hopesubject varchar2(50) not null,
	hits number not null,
	dateposted date not null,
	hopetime varchar2(100) not null,
	connectstate varchar2(20) not null,
	classteacherid varchar2(50) not null,
	studentid varchar2(50) not null,
	CONSTRAINT FK_lesson_student 
	foreign key(studentid) 
	references wmt_student(studentid),
	
	CONSTRAINT FK_lesson_classteacherid 
	foreign key(classteacherid) 
	references wmt_teacher(teacherid)
)
create sequence lessonno_seq;
drop table 


 